package com.wind.test;

import com.wind.entity.Cache;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import javax.persistence.*;
import java.util.List;

public class JpaTest07_JPQL_分组排序 {

  private EntityManagerFactory entityManagerFactory;
  private EntityManager entityManager;
  private EntityTransaction entityTransaction;

  @Before
  public void init() {
    entityManagerFactory = Persistence.createEntityManagerFactory("JpaDemo");
    entityManager = entityManagerFactory.createEntityManager();
    entityTransaction = entityManager.getTransaction();
    entityTransaction.begin();
  }

  @After
  public void destory() {
    entityTransaction.commit();
    entityManager.close();
    entityManagerFactory.close();
  }

  /*
    排序
   */
  @Test
  public void testOrderByAndGroupBy() {
    String jpql = "FROM Cache WHERE age > ? ORDER BY age ASC "; // DESC
    Query query = entityManager.createQuery(jpql).setParameter(new Integer(1), 20);
    List resultList = query.getResultList();
    System.out.println(resultList);
  }

  /*
    分组 + HAVING 子查询
   */
  @Test
  public void testHaving() {
    String jpql = "SELECT name FROM Cache " +
      "WHERE age > ? " +
      "GROUP BY age " +
      "HAVING count(age) >= 2";
    Query query = entityManager.createQuery(jpql).setParameter(new Integer(1), 20);
    List<Cache> resultList = query.getResultList();
    System.out.println(resultList);
  }

}
